CREATE EXTENSION
CREATE EXTENSION — Install an extension
Synopsis
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema_name ]
[ VERSION version ]
[ CASCADE ]
Description
CREATE EXTENSION loads a new extension into the current database. An extension with the same name must not already be loaded.
Loading an extension essentially runs the extension's script file. The script typically creates new SQL objects such as functions, data types, operators, and index support methods. CREATE EXTENSION additionally records the identities of all created objects so that they can be dropped when DROP EXTENSION is issued. The user running CREATE EXTENSION becomes the owner of the extension for subsequent privilege checks, and typically also becomes the owner of any objects created by the extension's script.
Typically, loading an extension requires the same privileges needed to create the extension's component objects. For many extensions, this means superuser privileges are required. However, if the extension is marked as trusted in its control file, it can be installed by any user with CREATE privilege on the current database. In this case, the extension object itself is owned by the calling user, but the contained objects are owned by the bootstrap superuser (unless the extension's script explicitly assigns them to the calling user). This configuration gives the calling user the right to drop the extension but not to modify individual objects within it.
Parameters
IF NOT EXISTS
Do not throw an error if an extension with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing extension bears any similarity to the one that would be created from the currently available script files.
extension_name
The name of the extension to install. PostgreSQL will use the directives in the file SHAREDIR/extension/extension_name.control to create the extension.
schema_name
The name of the schema in which to install the extension's objects, assuming the extension allows its contents to be relocated. The named schema must already exist. If not specified and the extension's control file does not specify a schema either, the current default object creation schema will be used.
If the extension specifies a schema parameter in its control file, the schema cannot be overridden with the SCHEMA clause. Typically, if a SCHEMA clause is given and it conflicts with the extension's schema parameter, an error occurs. However, if a CASCADE clause is also given, schema_name is ignored in the event of a schema conflict. The given schema_name will be used to install any required extensions that do not specify a schema in their control files.
Remember that the extension itself is not considered to be in any schema: extensions have unqualified names and must be unique across the entire database. However, objects belonging to the extension can be in schemas.
version
The version of the extension to install. This can be written as an identifier or a string. The default version is specified in the extension's control file.
CASCADE
Automatically install any uninstalled extensions that this extension depends on. Their dependencies will likewise be automatically installed. If a SCHEMA clause is given, it applies to all extensions installed in this manner. Other options of this statement are not applied to automatically installed extensions. In particular, the default versions of these automatically installed extensions will be selected.
Notes
Before using CREATE EXTENSION to load an extension into a database, the extension's support files must first be installed.
Currently available extensions for loading can be seen in the system views pg_available_extensions or pg_available_extension_versions.
Examples
# Install the hstore extension into the current database, placing its objects in the addons schema:
CREATE EXTENSION hstore SCHEMA addons;
# Alternative method:
SET search_path = addons;
CREATE EXTENSION hstore;